package com.xiia.dao;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;

import com.xiia.util.DBConnect;
import com.xiia.util.Pager;
import com.xiia.vo.Menu;
import com.xiia.vo.User;

/**
 * 2014 11/3 18:33
 * 
 * @author 李跃磊
 *
 */
public class MenuDao extends BaseDao {

	/**
	 * 按照栏目的状态查询栏目,如果增加成功返回List<Menu>否则返回NULL
	 * 
	 * @param status
	 * @return 栏目对象集合
	 */
	public List<Menu> select(int status) {
		String sql = "select * from t_menu where status = ?";
		Object[] params = { status };
		@SuppressWarnings("unchecked")
		List<Menu> menus = find(sql, params, Menu.class);
		if (menus.isEmpty()) {
			return null;
		}
		return menus;
	}

	/**
	 * 查询所有栏目,如果增加成功返回List<Menu>否则返回NULL
	 * 
	 * @return 栏目对象集合
	 */
	public List<Menu> selectAll() {
		String sql = "select * from t_menu";
		Object[] params = null;
		@SuppressWarnings("unchecked")
		List<Menu> menus = find(sql, params, Menu.class);
		if (menus != null) {
			return menus;
		}
		return null;
	}

	/**
	 * 按显示顺序查询栏目（前台）,查询成功返回Map<String, List<Menu>>否则返回NULL
	 * 
	 * @return 栏目对象集合
	 */
	public Map<String, List<Menu>> selectByDisOrder() {
		Map<String, List<Menu>> map = new HashMap<String, List<Menu>>();
		List<Menu> menus = selectMenuByRankAndStatus(1, 5);
		if (menus == null) {
			return null;
		}
		for (Menu menu : menus) {
			List<Menu> childMenu = selectByPreMenuId(menu);
			if (childMenu != null) {
				map.put(menu.getMenuName(), childMenu);
			}
		}
		map.put("fatherMenu", menus);
		return map;
	}

	/**
	 * 按等级和状态并按显示顺序的升序查询
	 * 
	 * @param menuRank
	 *            栏目等级
	 * @param status
	 *            栏目状态
	 * @return 栏目集合。
	 */
	public List<Menu> selectMenuByRankAndStatus(int menuRank, int status) {
		String sql = "select * from t_menu where menuRank = ? and status = ? order by displayOrder asc";
		Object[] params = { menuRank, status };
		@SuppressWarnings("unchecked")
		List<Menu> menus = find(sql, params, Menu.class);
		if (menus.isEmpty()) {
			return null;
		}
		return menus;
	}

	/**
	 * 查询当前栏目的子栏目，查询成功返回List<Menu>否则NULL
	 * 
	 * @param menu
	 * @return 栏目对象集合
	 */
	public List<Menu> selectByPreMenuId(Menu menu) {
		String sql = "select * from t_menu where preMenuId = ? and status = ? order by displayOrder asc";
		Object[] params = { menu.getMenuId(), menu.getStatus() };
		@SuppressWarnings("unchecked")
		List<Menu> menus = find(sql, params, Menu.class);
		if (menus.isEmpty()) {
			return null;
		}
		return menus;
	}

	/**
	 * 根据用户查询自己添加的栏目
	 * 
	 * @param user
	 * @return
	 */
	public List<Menu> selectMenuByUser(User user) {
		String sql = "select * from t_menu where userId = ?";
		Object[] params = { user.getUserId() };
		@SuppressWarnings("unchecked")
		List<Menu> menus = find(sql, params, Menu.class);
		if (menus.isEmpty()) {
			return null;
		}
		return menus;
	}

	/**
	 * 根据用户和栏目查询栏目
	 * 
	 * @param i
	 *            状态
	 * @param user
	 *            用户
	 * @return 栏目对象集合
	 */
	public List<Menu> selectMenuByUserAndStatus(int i, User user) {
		String sql = "select * from t_menu where status = ? and userId = ?";
		Object[] params = { i, user.getUserId() };
		@SuppressWarnings("unchecked")
		List<Menu> menus = find(sql, params, Menu.class);
		if (menus.isEmpty()) {
			return null;
		}
		return menus;
	}

	/**
	 * 增加栏目，如果增加成功返回Menu否则返回NULL
	 * 
	 * @param menu
	 *            栏目对象
	 * @return 栏目对象
	 */
	public Menu add(Menu menu) {
		String sql = "insert into t_menu(menuName,link,createTime,lastUpdateTime,menuRank,"
				+ "preMenuId,displayOrder,status,userId) values(?,?,?,?,?,?,?,?,?)";
		Object[] params = { menu.getMenuName(), menu.getLink(),
				menu.getCreateTime(), menu.getLastUpdateTime(),
				menu.getMenuRank(), menu.getPreMenuId(),
				menu.getDisplayOrder(), menu.getStatus(), menu.getUserId() };
		int i = update(sql, params);
		if (i == 0) {
			return null;
		}
		return menu;
	}

	/**
	 * 删除栏目，删除成功返回List<Menu>否则NULL
	 * 
	 * @param menus
	 *            栏目集合
	 * @return 栏目集合
	 */
	public List<Menu> delete(List<Menu> menus) {
		String sql = "delete from t_menu where menuId = ?";
		String sql1 = "delete from t_menu where preMenuId = ?";
		List<Menu> menus1 = null;
		try {
			conn = DBConnect.getConnect();
			conn.setAutoCommit(false);
			QueryRunner query = new QueryRunner();
			for (Menu menu : menus) {
				Object[] params = { menu.getMenuId() };
				query.update(conn, sql1, params);
			}
			for (Menu menu : menus) {
				Object[] params = { menu.getMenuId() };
				query.update(conn, sql, params);
			}
			menus1 = menus;
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}
		}
		return menus1;
	}

	/**
	 * 修改栏目的名称或者链接，修改成功返回List<Menu>否则NULL
	 * 
	 * @param menus
	 * @return
	 */
	public List<Menu> update(List<Menu> menus) {
		String sql = "update t_menu set menuName = ? ,link = ?,lastUpdateTime = ?,status = ? where menuId = ?";
		for (Menu menu : menus) {
			Object[] params = { menu.getMenuName(), menu.getLink(),
					menu.getLastUpdateTime(), 1, menu.getMenuId() };
			if (update(sql, params) == 0) {
				return null;
			}
		}
		return menus;
	}

	/**
	 * 修改栏目是否显示在前台，修改成功返回List<Menu>否则NULL
	 * 
	 * @param menus
	 * @return 栏目对象集合
	 */
	public List<Menu> updateStatusToShowOrUndo(List<Menu> menus) {
		String sql = "update t_menu set status = ?,lastUpdateTime = ? where menuId = ?";
		for (Menu menu : menus) {
			Object[] params = { menu.getStatus(), menu.getLastUpdateTime(),
					menu.getMenuId() };
			if (update(sql, params) == 0) {
				return null;
			}
		}
		return menus;
	}

	/**
	 * 修改栏目的显示顺序
	 * 
	 * @param menus
	 * @return 栏目对象集合
	 */
	public List<Menu> updatedisplayOrder(List<Menu> menus) {
		String sql = "update t_menu set lastUpdateTime = ?,displayOrder = ? where menuId = ?";
		for (Menu menu : menus) {
			Object[] params = { menu.getLastUpdateTime(),
					menu.getDisplayOrder(), menu.getMenuId() };
			if (update(sql, params) == 0) {
				return null;
			}
		}
		return menus;
	}

	public List<Menu> updateStatus(List<Menu> menus) {
		String sql1 = "update t_menu set status = ? where menuId = ?";
		String sql2 = "insert into t_check(checkedId,type,flag,msg,lastcheckTime,userId) values(?,?,?,?,?,?)";
		List<Menu> menus1 = null;
		try {
			conn = DBConnect.getConnect();
			conn.setAutoCommit(false);
			QueryRunner query = new QueryRunner();
			// 首先修改t_menu表里面的栏目状态为2（待审核）
			for (Menu menu : menus) {
				Object[] params1 = { 2, menu.getMenuId() };
				query.update(conn, sql1, params1);// 修改t_menu中的指定id记录的status字段为2
			}

			// 把记录插入到t_menu表
			for (Menu menu : menus) {
				Object[] params2 = { menu.getMenuId(), "menu", 0, null, null, 0 };
				query.update(conn, sql2, params2);
			}
			menus1 = menus;
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			}
		}
		return menus1;
	}

	/**
	 * 计算栏目表总记录条数
	 * 
	 * @return 总记录条数
	 */
	public int getCount() {
		String sql = "select count(*) count from t_menu";
		return getCountFromTable(sql, null);
	}

	/**
	 * 按页查询所有栏目表
	 * 
	 * @param page
	 *            页对象
	 * @return 页对象
	 */
	public Pager selectMenuByPage(Pager page) {
		String sql = "select * from t_menu limit ? , ?";
		String sql1 = "select * from t_menu where menuId = ?";
		Object[] params = { (page.getCurrentPage() - 1) * page.getPageSize(),
				page.getPageSize() };
		@SuppressWarnings("unchecked")
		List<Menu> menus = find(sql, params, Menu.class);
		for (Menu menu : menus) {
			Object[] params1 = { menu.getPreMenuId() };
			Menu m = (Menu) findObject(sql1, params1, Menu.class);
			menu.setMenu(m);
		}
		page.setList(menus);
		return page;
	}

	/**
	 * 按页查询status状态的栏目对象
	 * 
	 * @param page
	 * @param status
	 * @return 页对象
	 */
	public Pager selectMenuByPage(Pager page, int status) {
		String sql = "select * from t_menu where status = ? limit ? , ?";
		String sql1 = "select * from t_menu where menuId = ?";
		Object[] params = { status,
				(page.getCurrentPage() - 1) * page.getPageSize(),
				page.getPageSize() };
		@SuppressWarnings("unchecked")
		List<Menu> menus = find(sql, params, Menu.class);
		for (Menu menu : menus) {
			Object[] params1 = { menu.getPreMenuId() };
			Menu m = (Menu) findObject(sql1, params1, Menu.class);
			menu.setMenu(m);
		}
		page.setList(menus);
		return page;
	}

	/**
	 * 计算栏目在status状态的栏目总条数
	 * 
	 * @param status
	 * @return 总条数
	 */
	public int getCount(int status) {
		String sql = "select count(*) count from t_menu where status = ?";
		Object[] params = { status };
		return getCountFromTable(sql, params);
	}

	/**
	 * 计算状态为1和4的栏目的总条数
	 * 
	 * @return 总条数
	 */
	public int getCountCanUpdateMenu() {
		String sql = "select count(*) count from t_menu where status = 1 or status = 4";
		return getCountFromTable(sql, null);
	}

	/**
	 * 可修改的栏目，即状态为1或4状态的栏目
	 * 
	 * @param page
	 * @return 页对象
	 */
	public Pager getCanUpdateMenu(Pager page) {
		String sql = "select * from t_menu where status =1 or status = 4 limit ?,?";
		String sql1 = "select * from t_menu where menuId = ?";
		Object[] params = { (page.getCurrentPage() - 1) * page.getPageSize(),
				page.getPageSize() };
		@SuppressWarnings("unchecked")
		List<Menu> menus = find(sql, params, Menu.class);
		for (Menu menu : menus) {
			Object[] params1 = { menu.getPreMenuId() };
			Menu m = (Menu) findObject(sql1, params1, Menu.class);
			menu.setMenu(m);
		}
		page.setList(menus);
		return page;
	}
}
